﻿Public Class Modelling_Process

    Public StagesSQL As String
    Public IsStage As Boolean
    Public IsAllocationLine As Boolean
    Public ReturnSQL As Boolean = False
    Public TheRID As String = ""
    Public CanClearTempData As Boolean = True
    Public Session_ID_Main As String = ""
    Public Process_Period_ID As Integer = 0
    Public IsReportDesigner As Boolean = False

    Private Sub Modelling_Process_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load

    End Sub


    Private Sub ClearTempData()
        Dim sql As String = ""

        SetLabelText_ThreadSafe(CurrentLabel, "Clearing temporary data...")

        sql = "delete from dbo.Modelling_Products_Data where User_Name = '" & CurrentUser.Name & "'"

        RunNonQuery(sql)

    End Sub

    Public Function GetProductName(ByVal Product_Number As Integer, ByVal Scenario_ID As String) As String
        Dim sql As String
        Dim TheView As DataView

        sql = "select * from Modelling_Products where Scenario_ID = '" & Scenario_ID & "' and Product_Number = " & Product_Number
        TheView = LoadDataView(sql)

        GetProductName = TheView(0).Item("Product_Description").ToString
        TheView = Nothing

    End Function

    Private Function GetDatacutDescription(ByVal TableName As String, ByVal Datacut_ID As String) As String

        Dim TheData As DataView
        Dim sql As String

        sql = "Select top 1 Description from " & TableName & " where DataCut_ID = '" & Datacut_ID & "'"

        TheData = LoadDataView(sql)

        If TheData.Count = 0 Then
            GetDatacutDescription = ""
        Else
            GetDatacutDescription = TheData(0).Item("Description").ToString
        End If

    End Function

    Private Function ExtractStageNumber(ByVal Scenario_ID As String, ByVal Datacut_ID As String)
        Dim t As String

        t = Mid(Datacut_ID, Len(Trim(Scenario_ID)) + 6, Len(Datacut_ID))

        Return t

    End Function

    Private Function ExtractAllocation_ID(ByVal Scenario_ID As String, ByVal Datacut_ID As String)
        Dim t As String

        t = Mid(Datacut_ID, Len(Trim(Scenario_ID)) + 1, 5)

        Return t

    End Function

    Private Function Product_SQL(Scenario_ID As String, Allocation_ID As List(Of String), Product_Number As Integer, IsStage As Boolean, Show_All_Fields As Boolean, Session_ID As String, Prepare_Datacuts As Boolean, MainCall As Boolean, Period_ID As Integer, Optional ByVal RID As String = "") As String
        'only call me from the background worker

        Dim MainRun As Boolean = True

        If Session_ID = "" Then
            Session_ID = Format(Now, "ddMMyyhhmmssff")
            MainRun = True
        Else
            MainRun = False
        End If

        MainRun = MainCall

        Dim sql As String = ""
        Dim c As Integer = 0
        Dim Steps As DataView = Nothing
        Dim Steprec As DataRowView = Nothing
        Dim ts As String = ""
        Dim TheValue As String = ""
        Dim TheAnswer As String = ""
        Dim Learners As String = ""

        Dim ProggerPos As Integer = 0

        Dim cr As Integer = 0
        Dim sr As Integer = 0

        Dim ListOfDatacuts As New List(Of String)

        Dim SelectPart As String = ""
        Dim FromPart As String = ""

        Dim ConditionAnswer As String = ""
        Dim Condition As String = ""

        'set up the allocation_ID string
        Dim Allocation_ID_String As String = ""

        If IsStage Then
            If Allocation_ID Is Nothing Then
                Return ""
                Exit Function
            End If

            If Allocation_ID.Count = 0 Then
                Return ""
                Exit Function
            End If

            For c = 0 To Allocation_ID.Count - 1
                If Allocation_ID_String = "" Then
                    Allocation_ID_String = "'" & Allocation_ID(c) & "'"
                Else
                    Allocation_ID_String = Allocation_ID_String & " ,'" & Allocation_ID(c) & "'"
                End If
            Next
        End If


        If IsStage Then
            sql = "select [Scenario_ID],[Allocation_ID],[Stage_Number] as Product_Number,[Step_Number],[Operator],[Table_Name],[DataCut_ID],[DataCut_ID_Exception],[Condition],[Table_Name_Condition],[DataCut_ID_Condition],[Constant],[Constant_Exception],[Learner_Flag],[Table_Name_Exception],[Table_Name_Condition_Value],[DataCut_ID_Condition_Value] from Modelling_Steps where Scenario_ID = '" & Scenario_ID & "' " & _
              "and Allocation_ID = '" & Allocation_ID(0) & "' and Stage_Number = " & Product_Number & " " & _
              "order by Step_Number"
        Else
            sql = "select [Scenario_ID],[Product_Number],[Step_Number],[Operator],[Table_Name],[DataCut_ID],[DataCut_ID_Exception],[Condition],[Table_Name_Condition],[DataCut_ID_Condition],[Constant],[Constant_Exception],[Learner_Flag],[Table_Name_Exception],[Table_Name_Condition_Value],[DataCut_ID_Condition_Value] from Modelling_Steps_Products where Scenario_ID = '" & Scenario_ID & "' " & _
             "and Product_Number = " & Product_Number & " " & _
             "order by Step_Number"

            ''prepare the data for the product
            'If Prepare_Datacuts Then Prepare_Data_Cuts(Scenario_ID, "", Product_Number, Session_ID, False)

        End If

        Steps = LoadDataView(sql)

        If Steps.Count = 0 Then
            'send a blank row'
            Return "Select null as RID, null as Provider_Type_Code, 0 as [Final Result], 0 as Learners "
            Exit Function
        End If

        If Show_All_Fields = True Then
            SelectPart = "Select R.RID, R.UPIN, R.Provider_Name as [Provider Name], R.LA_Code as [Local Authority Code], R.LA_Name as [Local Authority], R.Region_Code as Region, R.Provider_Type_Description as [Provider Type], R.Provider_Subtype_Description as [Sub Type] "
        Else
            SelectPart = "Select R.RID "
        End If

        FromPart = "From All_Provider_Fields R "

        ts = ""

        For Each Steprec In Steps
            Dim Datacut_ID As String = ""
            Dim Table_Name As String = ""
            Dim JoinName As String = ""
            Dim ValueName As String = ""
            Dim SelectTable As String = ""

            If MainRun Then
                BGW.ReportProgress(CInt((ProggerPos / Steps.Count) * 100), "MAIN")
            Else
                BGW.ReportProgress(CInt((ProggerPos / Steps.Count) * 100), "SUB")
            End If

            'main part of step
            If MainRun Then
                SetLabelText_ThreadSafe(MainLabel, "Step " & Steprec.Item("Step_Number"))
            End If

            If BGW.CancellationPending Then
                Return ""
                Exit Function
            End If

            Select Case Trim(Steprec.Item("Table_Name").ToString)
                Case "zzConstant"
                    'then dont do anything yet, because its only a value
                    Datacut_ID = "zzConstant"
                    cr = cr + 1
                    ValueName = "Constant" & cr.ToString
                Case "aaProducts"
                    Datacut_ID = Trim(Scenario_ID) & Trim(Steprec.Item("Datacut_ID"))
                    Table_Name = "Modelling_Products_Data"
                    JoinName = "[" & Table_Name & Datacut_ID & "P]"
                    ValueName = Trim(GetProductName(Steprec.Item("Datacut_ID"), Scenario_ID))

                    Prepare_Data_Cuts(Scenario_ID, "", Steprec.Item("Datacut_ID"), Session_ID, False, Period_ID, RID)

                Case "bbStageData"
                    Datacut_ID = Trim(Steprec.Item("Datacut_ID"))
                    Table_Name = "Modelling_Products_Data"
                    JoinName = "[" & Table_Name & Datacut_ID & "S]"
                    sr = sr + 1
                    ValueName = "Stage" & sr.ToString

                    Prepare_Data_Cuts(Scenario_ID, ExtractAllocation_ID(Scenario_ID, Steprec.Item("Datacut_ID")), ExtractStageNumber(Scenario_ID, Steprec.Item("Datacut_ID")), Session_ID, True, Period_ID, RID)

                Case Else
                    Datacut_ID = Trim(Steprec.Item("Datacut_ID"))
                    Table_Name = Trim(Steprec.Item("Table_Name"))
                    JoinName = "[" & Table_Name & Datacut_ID & "D]"
                    ValueName = GetDatacutDescription(Table_Name, Datacut_ID)

            End Select

            If Datacut_ID <> "zzConstant" Then
                'assign the join name to the select table var so that you can use it in the case if there is an exception
                SelectTable = JoinName

                TheValue = "coalesce(cast(" & JoinName & ".Value as numeric(38,18)),0) "

                If DataCutUsed(ListOfDatacuts, Table_Name & Datacut_ID) = False Then
                    ts = ts & "left outer join "
                    ts = ts & "(select RID, Value from " & Table_Name & " "
                    ts = ts & "where DataCut_ID = '" & Datacut_ID & "' "

                    If Table_Name = "Modelling_Products_Data" Then
                        ts = ts & "and User_Name = '" & CurrentUser.Name & "' "
                        ts = ts & "and Session_ID = '" & Session_ID & "' "
                    End If

                    ts = ts & "and Status = 'Approved') " & JoinName & " "
                    ts = ts & "on (" & JoinName & ".RID = R.RID) "

                    ListOfDatacuts.Add(Table_Name & Datacut_ID)
                End If
            Else
                TheValue = Steprec.Item("Constant")
                SelectTable = "CONSTANT"
            End If

            If Show_All_Fields Then
                SelectPart = SelectPart & ", " & TheValue & " as [" & ValueName & "] "
            End If

            'exception part of step
            If Trim(Steprec.Item("DataCut_ID_Exception")) <> "none" Then

                Dim Table_Name_Exception As String = ""

                If Not IsDBNull(Steprec.Item("Table_Name_Exception")) Then
                    Table_Name_Exception = Trim(Steprec.Item("Table_Name_Exception"))

                End If

                Select Case Trim(Table_Name_Exception)
                    Case "zzConstant"
                        'then dont do anything yet, because its only a value
                        Datacut_ID = "zzConstant"
                        cr = cr + 1
                        ValueName = "Constant exception" & cr.ToString
                    Case "aaProducts"
                        Datacut_ID = Trim(Scenario_ID) & Trim(Steprec.Item("Datacut_ID_Exception"))
                        Table_Name = "Modelling_Products_Data"
                        JoinName = "[" & Table_Name & Datacut_ID & "P]"
                        ValueName = Trim(GetProductName(Steprec.Item("Datacut_ID_Exception"), Scenario_ID) & " exception")
                        Prepare_Data_Cuts(Scenario_ID, "", Steprec.Item("Datacut_ID_Exception"), Session_ID, False, Period_ID, RID)

                    Case "bbStageData"
                        Datacut_ID = Trim(Steprec.Item("Datacut_ID_Exception"))
                        Table_Name = "Modelling_Products_Data"
                        JoinName = "[" & Table_Name & Datacut_ID & "S]"
                        sr = sr + 1
                        ValueName = "Stage exception" & sr.ToString
                        Prepare_Data_Cuts(Scenario_ID, ExtractAllocation_ID(Scenario_ID, Steprec.Item("Datacut_ID_Exception")), ExtractStageNumber(Scenario_ID, Steprec.Item("Datacut_ID_Exception")), Session_ID, True, Period_ID, RID)
                    Case ""
                        Datacut_ID = Trim(Steprec.Item("Datacut_ID_Exception"))
                        JoinName = "[" & Table_Name & Datacut_ID & "D]"
                        ValueName = Trim(GetDatacutDescription(Table_Name, Datacut_ID) & " exception")
                    Case Else
                        Datacut_ID = Trim(Steprec.Item("Datacut_ID_Exception"))
                        Table_Name = Table_Name_Exception
                        ValueName = Trim(GetDatacutDescription(Table_Name, Datacut_ID) & " exception")
                        JoinName = "[" & Table_Name & Datacut_ID & "D]"
                End Select

                If Datacut_ID <> "zzConstant" Then

                    If SelectTable = "CONSTANT" Then
                        TheValue = "case when coalesce(" & JoinName & ".Value,0) = 0 then coalesce(" & TheValue & ",0) else " & JoinName & ".Value end "
                    Else
                        TheValue = "case when coalesce(" & JoinName & ".Value,0) = 0 then coalesce(" & SelectTable & ".Value,0) else " & JoinName & ".Value end "
                    End If


                    If DataCutUsed(ListOfDatacuts, Table_Name & Datacut_ID) = False Then

                        ts = ts & "left outer join "
                        ts = ts & "(select RID, Value from " & Table_Name & " "
                        ts = ts & "where DataCut_ID = '" & Datacut_ID & "' "

                        If Table_Name = "Modelling_Products_Data" Then
                            ts = ts & "and User_Name = '" & CurrentUser.Name & "' "
                            ts = ts & "and Session_ID = '" & Session_ID & "' "
                        End If

                        ts = ts & "and Status = 'Approved') " & JoinName & " "
                        ts = ts & "on (" & JoinName & ".RID = R.RID) "

                        ListOfDatacuts.Add(Table_Name & Datacut_ID)
                    End If
                Else
                    If SelectTable = "CONSTANT" Then
                        TheValue = "case when coalesce(" & Steprec.Item("Constant_Exception") & ",0) = 0 then coalesce(" & TheValue & ",0) else " & Steprec.Item("Constant_Exception") & " end "
                    Else
                        TheValue = "case when coalesce(" & Steprec.Item("Constant_Exception") & ",0) = 0 then coalesce(" & SelectTable & ".Value,0) else " & Steprec.Item("Constant_Exception") & " end "
                    End If
                End If

                If Show_All_Fields Then
                    If Datacut_ID <> "zzConstant" Then
                        SelectPart = SelectPart & ", coalesce(" & JoinName & ".Value,0 ) as [" & ValueName & "] "
                    Else
                        SelectPart = SelectPart & ", coalesce(" & Steprec.Item("Constant_Exception") & ",0 ) as [" & ValueName & "] "
                    End If

                    SelectPart = SelectPart & ", " & TheValue & " as [Value Used] "
                End If
            End If

            'condition part of step
            If Trim(Steprec.Item("Condition")) <> "no" Then

                Select Case Trim(Steprec.Item("Table_Name_Condition"))
                    Case "zzConstant"
                        'then dont do anything yet, because its only a value
                        Datacut_ID = "zzConstant"
                        cr = cr + 1
                        ValueName = "Constant condition" & cr.ToString
                    Case "aaProducts"
                        Datacut_ID = Trim(Scenario_ID) & Trim(Steprec.Item("Datacut_ID_Condition"))
                        Table_Name = "Modelling_Products_Data"
                        JoinName = "[" & Table_Name & Datacut_ID & "P]"
                        ValueName = Trim(GetProductName(Steprec.Item("Datacut_ID_Condition"), Scenario_ID) & " condition")
                        Prepare_Data_Cuts(Scenario_ID, "", Steprec.Item("Datacut_ID_Condition"), Session_ID, False, Period_ID, RID)

                    Case "bbStageData"
                        Datacut_ID = Trim(Steprec.Item("Datacut_ID_Condition"))
                        Table_Name = "Modelling_Products_Data"
                        JoinName = "[" & Table_Name & Datacut_ID & "S]"
                        sr = sr + 1
                        ValueName = "Stage condition" & sr.ToString
                        Prepare_Data_Cuts(Scenario_ID, ExtractAllocation_ID(Scenario_ID, Steprec.Item("Datacut_ID_Condition")), ExtractStageNumber(Scenario_ID, Steprec.Item("Datacut_ID_Condition")), Session_ID, True, Period_ID, RID)
                    Case Else
                        Datacut_ID = Trim(Steprec.Item("Datacut_ID_Condition"))
                        Table_Name = Trim(Steprec.Item("Table_Name_Condition"))
                        ValueName = Trim(GetDatacutDescription(Table_Name, Datacut_ID) & " condition")
                        JoinName = "[" & Table_Name & Datacut_ID & "D]"
                End Select

                If Datacut_ID <> "zzConstant" Then

                    Condition = " coalesce(" & JoinName & ".Value,0 ) "

                    If DataCutUsed(ListOfDatacuts, Table_Name & Datacut_ID) = False Then
                        ts = ts & "left outer join "
                        ts = ts & "(select RID, Value from " & Table_Name & " "
                        ts = ts & "where DataCut_ID = '" & Datacut_ID & "' "

                        If Table_Name = "Modelling_Products_Data" Then
                            ts = ts & "and User_Name = '" & CurrentUser.Name & "' "
                            ts = ts & "and Session_ID = '" & Session_ID & "' "
                        End If

                        ts = ts & "and Status = 'Approved') " & JoinName & " "
                        ts = ts & "on (" & JoinName & ".RID = R.RID) "

                        ListOfDatacuts.Add(Table_Name & Datacut_ID)
                    End If

                Else
                    Condition = Steprec.Item("Datacut_ID_Condition")
                End If

                If Show_All_Fields Then
                    SelectPart = SelectPart & ", " & Condition & " as [" & ValueName & "] "
                End If

                'The value to be used when the condition is true
                Select Case Trim(Steprec.Item("Table_Name_Condition_Value"))
                    Case "zzConstant"
                        'then dont do anything yet, because its only a value
                        Datacut_ID = "zzConstant"
                        cr = cr + 1
                        ValueName = "Constant condition answer" & cr.ToString
                    Case "aaProducts"
                        Datacut_ID = Trim(Scenario_ID) & Trim(Steprec.Item("Datacut_ID_Condition_Value"))
                        Table_Name = "Modelling_Products_Data"
                        JoinName = "[" & Table_Name & Datacut_ID & "P]"
                        ValueName = Trim(GetProductName(Steprec.Item("Datacut_ID_Condition_Value"), Scenario_ID) & " condition answer")
                        Prepare_Data_Cuts(Scenario_ID, "", Steprec.Item("Datacut_ID_Condition_Value"), Session_ID, False, Period_ID, RID)

                    Case "bbStageData"
                        Datacut_ID = Trim(Steprec.Item("Datacut_ID_Condition_Value"))
                        Table_Name = "Modelling_Products_Data"
                        JoinName = "[" & Table_Name & Datacut_ID & "S]"
                        sr = sr + 1
                        ValueName = "Stage condition answer" & sr.ToString
                        Prepare_Data_Cuts(Scenario_ID, ExtractAllocation_ID(Scenario_ID, Steprec.Item("Datacut_ID_Condition_Value")), ExtractStageNumber(Scenario_ID, Steprec.Item("Datacut_ID_Condition_Value")), Session_ID, True, Period_ID, RID)
                    Case Else
                        Datacut_ID = Trim(Steprec.Item("Datacut_ID_Condition_Value"))
                        Table_Name = Trim(Steprec.Item("Table_Name_Condition_Value"))
                        ValueName = Trim(GetDatacutDescription(Table_Name, Datacut_ID) & " condition answer")
                        JoinName = "[" & Table_Name & Datacut_ID & "D]"
                End Select

                If Datacut_ID <> "zzConstant" Then

                    ConditionAnswer = " coalesce(" & JoinName & ".Value,0 ) "

                    If DataCutUsed(ListOfDatacuts, Table_Name & Datacut_ID) = False Then
                        ts = ts & "left outer join "
                        ts = ts & "(select RID, Value from " & Table_Name & " "
                        ts = ts & "where DataCut_ID = '" & Datacut_ID & "' "

                        If Table_Name = "Modelling_Products_Data" Then
                            ts = ts & "and User_Name = '" & CurrentUser.Name & "' "
                            ts = ts & "and Session_ID = '" & Session_ID & "' "
                        End If

                        ts = ts & "and Status = 'Approved') " & JoinName & " "
                        ts = ts & "on (" & JoinName & ".RID = R.RID) "

                        ListOfDatacuts.Add(Table_Name & Datacut_ID)
                    End If

                Else
                    ConditionAnswer = Steprec.Item("Datacut_ID_Condition_Value")
                End If

                If Show_All_Fields Then
                    SelectPart = SelectPart & ", " & ConditionAnswer & " as [" & ValueName & "] "
                End If

            End If

            If TheAnswer = "" Then
                TheAnswer = "coalesce(" & TheValue & ",0)"
            Else
                'check that you are not going to devide by zero
                If Steprec.Item("Operator").ToString = "/" Then
                    TheAnswer = "(coalesce(" & TheAnswer & " " & OperatorFix(Steprec.Item("Operator").ToString) & " NULLIF(coalesce(" & TheValue & ",0),0),0))"
                Else
                    TheAnswer = "(" & TheAnswer & " " & OperatorFix(Steprec.Item("Operator").ToString) & " coalesce(" & TheValue & ",0))"
                End If

                'do the condition here
                If Trim(Steprec.Item("Condition")) <> "no" Then
                    TheAnswer = " (case when " & TheAnswer & " " & Steprec.Item("Condition") & " " & Condition & " then " & ConditionAnswer & " else " & TheAnswer & " end) "

                End If

            End If

            If Steprec.Item("Learner_Flag") = 1 Then
                If Learners = "" Then
                    'only once please!
                    Learners = ", " & TheAnswer & " as Learners "
                End If
            End If

            If Show_All_Fields Then SelectPart = SelectPart & "," & TheAnswer & " as [Step " & Steprec.Item("Step_Number") & " Result] "

            ProggerPos = ProggerPos + 1
            If MainRun Then
                BGW.ReportProgress(CInt((ProggerPos / Steps.Count) * 100), "MAIN")
            Else
                BGW.ReportProgress(CInt((ProggerPos / Steps.Count) * 100), "SUB")
            End If



        Next

        FromPart = FromPart & ts

        Dim WhereClause As String = ""

        If Allocation_ID_String = "" Then
            WhereClause = " where R.Lead = 1"
        Else
            WhereClause = " where R.UPIN in ( " & _
                                   "select UPIN from Provider_Funding_Streams " & _
                                   "where Period_ID = " & Period_ID & " and Budget_ID in (Select Budget_ID from allocation_lines where Allocation_ID in (" & Allocation_ID_String & ")) " & _
                                   "and lead = 1)"
        End If

        If RID <> "" Then
            WhereClause = WhereClause & " and R.RID = '" & RID & "' "
        End If

        'check to make sure you always have learners
        If Learners = "" Then
            Learners = ", 0 as Learners "
        End If

        sql = SelectPart & "," & TheAnswer & " as [Final Result] " & Learners & FromPart & WhereClause

        Return sql

    End Function

    Public Function OperatorFix(ByVal TheOperator As String) As String

        If TheOperator = "x" Then
            OperatorFix = "*"
        Else
            OperatorFix = TheOperator
        End If
    End Function

    Private Sub Prepare_Data_Cuts(Scenario_ID As String, Allocation_ID As String, Product_Number As Integer, Session_ID As String, IsStage As Boolean, Period_ID As Integer, Optional ByVal RID As String = "")
        'Allocation ID will not be "" if it is a Stage, for products it will be ""

        Dim sql As String = ""
        Dim TheProducts As DataView = Nothing
        Dim rec As DataRowView = Nothing
        Dim c As Integer = 0

        Dim ProductsList As New List(Of String)
        Dim ProductsLeft As Boolean = True
        Dim PM As String = Product_Number

        ProductsList.Clear()


        If Not IsStage Then

            'Products
            'add the current product to the list
            ProductsList.Add(Product_Number)

            While ProductsLeft
                sql = "declare @Scenario_ID as nvarchar(15) " & _
                    "declare @Product_Number as int " & _
                    " " & _
                    "set @Scenario_ID = '" & Scenario_ID & "' " & _
                    " " & _
                    "select DataCut_ID as Product_Number from dbo.Modelling_Steps_Products " & _
                    "where scenario_id = @Scenario_ID " & _
                    "and Product_number in (" & PM & ")" & _
                    "and Table_Name= 'aaProducts' " & _
                    " " & _
                    "union all " & _
                    " " & _
                    "select DataCut_ID_Exception as Product_Number from dbo.Modelling_Steps_Products " & _
                    "where scenario_id = @Scenario_ID " & _
                    "and Product_number in (" & PM & ")" & _
                    "and Table_Name_Exception = 'aaProducts' " & _
                    " " & _
                    "union all " & _
                    " " & _
                    "select DataCut_ID_Condition as Product_Number from dbo.Modelling_Steps_Products " & _
                    "where scenario_id = @Scenario_ID " & _
                    "and Product_number in (" & PM & ")" & _
                    "and Table_Name_Condition = 'aaProducts' " & _
                    "and Condition <> 'no'"

                TheProducts = LoadDataView(sql)

                If TheProducts.Count <> 0 Then
                    PM = ""
                    For Each rec In TheProducts
                        If PM = "" Then
                            PM = Trim(rec("Product_Number"))
                        Else
                            PM = PM & ", " & Trim(rec("Product_Number"))
                        End If

                        Dim foundit As Boolean = False

                        For c = 0 To ProductsList.Count - 1
                            If ProductsList(c) = Trim(rec("Product_Number")) Then
                                foundit = True
                            End If
                        Next

                        If foundit = False Then
                            ProductsList.Add(Trim(rec("Product_Number")))
                        End If


                    Next

                    ProductsLeft = True
                Else
                    ProductsLeft = False
                End If
            End While

        Else
            'Stage
            If Allocation_ID <> "" Then
                Dim Datacut_ID As String

                Datacut_ID = Trim(Scenario_ID) & Trim(Allocation_ID) & Trim(Product_Number)
                '    Datacut_ID = Trim(Product_Number)
                ProductsList.Add(Datacut_ID)

            End If


        End If


        Dim Product_Datacut_ID As String = ""
        'now that we have the list of products lets run through them backwards

        If ProductsList.Count = 0 Then Exit Sub

        For c = ProductsList.Count - 1 To 0 Step -1

            If BGW.CancellationPending Then
                Exit Sub
            End If

            If Not IsStage Then

                SetLabelText_ThreadSafe(CurrentLabel, GetProductName(Trim(ProductsList(c)), Scenario_ID))

                Product_Datacut_ID = Trim(Scenario_ID) & Trim(ProductsList(c))

                'first see if this product already exists to avoid duplicating the operation
                sql = "select top 1 * from Modelling_Products_Data where Session_ID = '" & Session_ID & "' and Datacut_ID = '" & Product_Datacut_ID & "'"

                If LoadDataView(sql).Count = 0 Then
                    sql = Product_SQL(Scenario_ID, Nothing, Trim(ProductsList(c)), False, False, Session_ID, False, False, Period_ID, RID)
                Else
                    sql = ""
                End If

            Else
                Dim Alist As New List(Of String)
                Alist.Clear()
                Alist.Add(Allocation_ID)

                SetLabelText_ThreadSafe(CurrentLabel, "Calculating Step " & Allocation_ID & " - " & Trim(Product_Number))

                sql = Product_SQL(Scenario_ID, Alist, Trim(Product_Number), True, False, Session_ID, False, False, Period_ID, RID)

                Product_Datacut_ID = Trim(ProductsList(c))
            End If

            If sql <> "" Then
                'delete all rows from previous sessions
                RunNonQuery("delete from Modelling_Products_Data where User_Name = '" & CurrentUser.Name & "' and Datacut_ID = '" & Product_Datacut_ID & "' and Session_ID <> '" & Session_ID & "'")

                'now insert the rows
                'if it fails it means that this product's data has already been added for this session
                RunNonQuery("insert into Modelling_Products_Data select '" & Product_Datacut_ID & "' as DataCut_ID, RID, '" & CurrentUser.Name & "' as User_Name, 'Approved' as Status, [Final Result] as value, Learners, '" & Session_ID & "' as Session_ID from (" & sql & ") subatomic")
            End If
        Next


    End Sub

    Private Function DataCutUsed(ByVal ListOfCuts As List(Of String), ByVal Datacut_ID As String) As Boolean
        Dim r As Integer
        Dim Used As Boolean
        Used = False

        If ListOfCuts.Count = 0 Then
            Used = False
        Else
            For r = 0 To ListOfCuts.Count - 1
                If ListOfCuts(r) = Datacut_ID Then
                    Used = True
                End If
            Next
        End If

        DataCutUsed = Used
    End Function


    Private Function BuildStagesSQL(ByVal Scenario_ID As String) As String

        Dim SView As DataView

        Dim SRec As DataRowView

        Dim sql As String

        Dim FundingCase, LearnerCase, TotalFundingCase, TotalLearnerCase, TheCase As String

        FundingCase = ""
        LearnerCase = ""
        TotalFundingCase = ""
        TotalLearnerCase = ""
        TheCase = ""

        Dim NoOrderStagesSQL As String = ""

        NoOrderStagesSQL = Mid(StagesSQL, 1, StagesSQL.ToUpper.IndexOf("ORDER"))

        sql = "select distinct sub.allocation_id + " & _
            "case when len(cast(Stage_Number as nvarchar(max))) = 1 then '0' +  " & _
            "cast(Stage_Number as nvarchar(max)) else cast(Stage_Number as nvarchar(max)) end " & _
            "as Datacut_ID, Stage_Description, sub.Allocation_ID, Allocation_Description " & _
            "from (" & NoOrderStagesSQL & ") sub, allocation_lines al where sub.allocation_ID = al.Allocation_ID order by Datacut_ID"

        SView = LoadDataView(sql)

        For Each SRec In SView
            'do the funding case
            FundingCase = "sum(case when subsub.datacut_id = '" & SRec.Item("Datacut_ID") & "' then Value else 0 end) as [" & SRec.Item("Allocation_Description") & ": " & SRec.Item("Stage_Description") & "]"

            'do the learner case
            LearnerCase = "sum(case when subsub.datacut_id = '" & SRec.Item("Datacut_ID") & "' then Learners else 0 end) as [" & SRec.Item("Allocation_Description") & ": " & SRec.Item("Stage_Description") & " Learners]"

            If TheCase <> "" Then
                TheCase = TheCase & ", "
            End If
            TheCase = TheCase & FundingCase & ", " & LearnerCase

            'do the total funding case
            If TotalFundingCase <> "" Then
                TotalFundingCase = TotalFundingCase & " + "
            End If

            TotalFundingCase = TotalFundingCase & "sum(case when subsub.datacut_id = '" & SRec.Item("Datacut_ID") & "' then Value else 0 end)"

            'do the total learner case
            If TotalLearnerCase <> "" Then
                TotalLearnerCase = TotalLearnerCase & " + "
            End If

            TotalLearnerCase = TotalLearnerCase & "sum(case when subsub.datacut_id = '" & SRec.Item("Datacut_ID") & "' then Learners else 0 end)"

        Next

        TotalFundingCase = TotalFundingCase & " as [Final Result] "
        TotalLearnerCase = TotalLearnerCase & " as [Learners] "

        sql = "Select MPD.RID, " & _
            "UPIN,Provider_Name as [Provider Name],LA_Name [Local Authority],Region_Name [Region],Provider_Type_Description [Provider Type],Provider_Subtype_Description [Sub Type]," & _
            TheCase & ", " & TotalFundingCase & ", " & TotalLearnerCase & _
            " FROM " & _
            "(select sub.allocation_id + " & _
            "case when len(cast(Stage_Number as nvarchar(max))) = 1 then '0' +  " & _
            "cast(Stage_Number as nvarchar(max)) else cast(Stage_Number as nvarchar(max)) end " & _
            "as Datacut_ID, Stage_Description, sub.Allocation_ID, Allocation_Description " & _
            "from (select * from modelling_stages where allocation_id in (" & _
            "select distinct allocation_ID from (" & NoOrderStagesSQL & ") trickythis" & _
            ") and scenario_id = '" & Scenario_ID & "') sub, " & _
            "allocation_lines al where sub.allocation_ID = al.Allocation_ID) subsub, dbo.Modelling_Products_Data MPD, All_Provider_Fields APV " & _
            "where MPD.user_name = '" & CurrentUser.Name & "' and MPD.Datacut_ID = subsub.Datacut_ID " & _
            "and APV.RID = MPD.RID " & _
            "group by MPD.RID, UPIN, Provider_Name, LA_Name, Region_Name, Provider_Type_Description, Provider_Subtype_Description "


        Return sql
    End Function

    

    Private Sub StartProcess()
        Dim sql As String = ""
        Dim Scenario_ID As String
        Dim Allocation_ID As String
        Dim Product_Number As String
        Dim TheLabel As String
        Dim ShowAllFields As Boolean
        Dim Session_ID As String = ""
        Dim MainDataCutID As String = ""

        Dim c As Integer = 0


        Dim TheView As DataView
        Dim rec As DataRowView

        If CanClearTempData Then ClearTempData()

        If IsAllocationLine Then
            ShowAllFields = False
        Else
            If IsReportDesigner Then
                ShowAllFields = False
            Else
                ShowAllFields = True
            End If

        End If

        TheView = LoadDataView(StagesSQL)

        If TheView.Count = 0 Then
            'this product is no more
        Else
            Scenario_ID = TheView(0).Item("Scenario_ID")
        End If


        If Session_ID_Main = "" Then
            Session_ID = Format(Now, "ddMMyyhhmmssff")
        Else
            Session_ID = Session_ID_Main
        End If

        For Each rec In TheView


            Dim AID As New List(Of String)
            If IsStage Then
                Allocation_ID = rec.Item("Allocation_ID")
                Product_Number = rec.Item("Stage_Number")
                TheLabel = rec.Item("Stage_Description")
            Else
                Allocation_ID = ""
                Product_Number = rec.Item("Product_Number")
                TheLabel = rec.Item("Product_Description")
            End If

            SetLabelText_ThreadSafe(TotalLabel, TheLabel)

            AID.Add(Allocation_ID)


            sql = Product_SQL(Scenario_ID, AID, Product_Number, IsStage, ShowAllFields, Session_ID, True, True, Process_Period_ID, TheRID)

            If IsAllocationLine Then

                Dim MainSessionID As String

                MainDataCutID = Trim(Allocation_ID) & IIf(Len(Trim(Product_Number)) = 1, "0" & Trim(Product_Number), Trim(Product_Number))

                MainSessionID = Session_ID & "MAIN"
                'MainSessionID = Session_ID

                'now insert the rows
                'if it fails it means that this product's data has already been added for this session
                RunNonQuery("insert into Modelling_Products_Data select '" & MainDataCutID & "' as DataCut_ID, RID, '" & CurrentUser.Name & "' as User_Name, 'Approved' as Status, [Final Result] as value, Learners, '" & MainSessionID & "' as Session_ID from (" & sql & ") subatomic")
            End If

            c = c + 1

            BGW.ReportProgress(CInt((c / TheView.Count) * 100), "TOTAL")

        Next

        BGW.ReportProgress(100, "TOTAL")

        If Not BGW.CancellationPending Then

            If IsAllocationLine Then
                sql = BuildStagesSQL(Scenario_ID)
            End If

            SetModelSQL_ThreadSafe(SQLTextBox, sql)
        End If

    End Sub

    Private Sub BGW_DoWork(sender As System.Object, e As System.ComponentModel.DoWorkEventArgs) Handles BGW.DoWork
        StartProcess()

        If BGW.CancellationPending Then
            e.Cancel = True
        End If

    End Sub

    Private Sub BGW_ProgressChanged(sender As System.Object, e As System.ComponentModel.ProgressChangedEventArgs) Handles BGW.ProgressChanged

        Select Case e.UserState
            Case "MAIN"
                Progger.Value = e.ProgressPercentage
            Case "TOTAL"
                TotalProgger.Value = e.ProgressPercentage
            Case Else
                SubProgger.Value = e.ProgressPercentage
        End Select


    End Sub

    'The Labels
    ' The delegate
    Delegate Sub SetLabelText_Delegate(ByVal [Label] As Label, ByVal [text] As String)

    ' The delegates subroutine.
    Private Sub SetLabelText_ThreadSafe(ByVal [Label] As Label, ByVal [text] As String)
        ' InvokeRequired required compares the thread ID of the calling thread to the thread ID of the creating thread.
        ' If these threads are different, it returns true.
        If [Label].InvokeRequired Then
            Dim MyDelegate As New SetLabelText_Delegate(AddressOf SetLabelText_ThreadSafe)
            Me.Invoke(MyDelegate, New Object() {[Label], [text]})
        Else
            [Label].Text = [text]
        End If
    End Sub

    'The SQL Statement
    Delegate Sub SetModelSQL_Delegate(ByVal [TheTextBox] As TextBox, ByVal [SQL] As String)

    ' The delegates subroutine.
    Private Sub SetModelSQL_ThreadSafe(ByVal [TheTextBox] As TextBox, ByVal [SQL] As String)

        If [TheTextBox].InvokeRequired Then
            Dim MyDelegate As New SetModelSQL_Delegate(AddressOf SetModelSQL_ThreadSafe)
            Me.Invoke(MyDelegate, New Object() {[TheTextBox], [SQL]})
        Else
            [TheTextBox].Text = [SQL]
        End If

    End Sub


    Private Sub BGW_RunWorkerCompleted(sender As System.Object, e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles BGW.RunWorkerCompleted

        If e.Cancelled Then
            DialogResult = Windows.Forms.DialogResult.Cancel

            Me.Close()
            MessageBox.Show("Cancelled by user", "Cancelled", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Else

            SubProgger.Value = 100
            Progger.Value = 100

            If ReturnSQL Then
                DialogResult = Windows.Forms.DialogResult.OK
            Else
                With Modelling_ResultsForm
                    .ResultsGrid.AutoGenerateColumns = True
                    .ResultsGrid.Columns.Clear()

                    If IsStage Then
                        .TheBS.DataSource = LoadDataTable(SQLTextBox.Text)
                    Else
                        .TheBS.DataSource = LoadDataTable(SQLTextBox.Text & " and R.Provider_Subtype_Code <> 'ZCONV'")
                    End If

                    .Text = Me.Text & " Results"

                    .Show()
                    .Focus()
                End With

            End If

            Me.Close()
            'MessageBox.Show("Done")
        End If

    End Sub

    Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
        If MessageBox.Show("Are you sure you want to cancel the process?", "Cancel?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
            If BGW.IsBusy Then
                BGW.CancelAsync()
            Else
                Me.Close()
            End If
        End If
    End Sub

    Private Sub Modelling_Process_Shown(sender As System.Object, e As System.EventArgs) Handles MyBase.Shown
        SQLTextBox.Text = ""
        CurrentLabel.Text = "Current Process"
        MainLabel.Text = "Main Process"
        TotalLabel.Text = "Total Process"
        Progger.Value = 0
        SubProgger.Value = 0
        TotalProgger.Value = 0

        BGW.RunWorkerAsync()
    End Sub
End Class